
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.uoc.tdp.pac4.only5.manager.impl;

import edu.uoc.tdp.pac4.only5.domain.Rol;
import edu.uoc.tdp.pac4.only5.domain.User;
import edu.uoc.tdp.pac4.only5.manager.DatabaseConnectionManager;
import edu.uoc.tdp.pac4.only5.manager.UserManager;
import edu.uoc.tdp.pac4.only5.exceptions.EespaisDatabaseException;
import java.rmi.RemoteException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Josep
 * @author Gerard
 */
public class UserManagerImpl extends AbstractManager implements UserManager {

    public UserManagerImpl(DatabaseConnectionManager databaseConnectionManager) throws RemoteException {
        super(databaseConnectionManager);
    }
    
    /**
     * @autor Alex
     * @param user
     * @return void
     */
    @Override
    public void addUser(User user) throws RemoteException {
        try {
            String insertStatement = "INSERT INTO users "
                    + "(userid,password,name,surname1,surname2,company,address,postal_code,city,telephone,"
                    + "mobile_phone,roleid,email,"
                    + "creation_date,creation_user,inactivation_date,inactivation_user,record_date) "
                    + "VALUES ('" + user.getUserId()
                    + "'," + user.getPassword() + ",'" + user.getName() + "','" + user.getSurname1() + "','" 
                    + user.getSurname2() + "','" + user.getCompany() + "','" + user.getAdress() + "'," 
                    + user.getPostalCode() + ",'" + user.getCity() + "'," + user.getTelephone() + "," 
                    + user.getMobileTelephone() + "," + user.getRoleId() + ",'" + user.getEmail() + "',"
                    + "'01-01-01',' ','01-01-01',' ','01-01-01');";
            
            StringBuilder sql = new StringBuilder();
            sql.append(insertStatement);
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(NullPointerException e) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    /**
     * @autor Alex
     * @param user
     * @return void
     */
    @Override
    public void modifyUser(User user) throws RemoteException {
        try {
            String insertStatement = "UPDATE users set "
                    + "password='" + user.getPassword() + "',name='" + user.getName() + "',surname1='" + user.getSurname1() 
                    + "',surname2='" + user.getSurname2() + "',company='" + user.getCompany() + "',address='" + user.getAdress() 
                    + "',postal_code=" + user.getPostalCode() + ",city='" + user.getCity() + "',telephone=" + user.getTelephone()
                    + ",mobile_phone=" + user.getMobileTelephone() + ",roleid=" + user.getRoleId() + ",email='" + user.getEmail()
                    + "' WHERE userid= ?";
            
            StringBuilder sql = new StringBuilder();
            sql.append(insertStatement);
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setString(1, user.getUserId());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }  catch(NullPointerException e) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    /**
     * @autor Alex
     * @param userId
     * @return void
     */
    @Override
    public void deleteUser(String userId) throws RemoteException {
        User user = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM users WHERE USERID = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setString(1, userId);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }  catch(NullPointerException e) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
        
       return;
    }

    @Override
    public List<User> getUsers() throws RemoteException {
       List<User> users = new ArrayList<User>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM users");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                users.add(getUser(resultSet));
            }
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }
        
       return users;
    }

    /**
     * @autor Gerard
     * @param userId
     * @return 
     */
    @Override
    public User getUserById(String userId) throws RemoteException, EespaisDatabaseException {
        User user = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM users WHERE USERID = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setString(1, userId);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){
                user = getUser(resultSet);
            }
        } catch(SQLException ex){
           throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
        
       return user;
    }
    
    
    /**
     * @autor Gerard
     * @param rs
     * @return
     * @throws SQLException 
     */
    private User getUser(ResultSet rs) throws RemoteException, SQLException{
        User user = new User();    
        user.setAdress(rs.getString("address"));
        user.setCity(rs.getString("city"));
        user.setCompany(rs.getString("company"));
        user.setEmail(rs.getString("email"));
        user.setMobileTelephone(rs.getInt("mobile_phone"));
        user.setName(rs.getString("name"));
        user.setPassword(rs.getString("password"));
        user.setPostalCode(rs.getInt("postal_code"));
        user.setRoleId(rs.getInt("roleid"));
        user.setSurname1(rs.getString("surname1"));
        user.setSurname2(rs.getString("surname2"));
        user.setTelephone(rs.getInt("telephone"));
        user.setUserId(rs.getString("userid"));
        user.setCreationDate(rs.getDate("creation_date"));
        user.setCreationUser(rs.getString("creation_user"));
        user.setInactivationDate(rs.getDate("inactivation_date"));
        user.setInactivationUser(rs.getString("inactivation_user"));
        user.setRecordDate(rs.getDate("record_date"));
        return user;
    }

    @Override
    public List<User> getUsersByName(String name) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void addRol(Rol rol) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void modifyRol(Rol rol) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void deleteRol(Integer roleId) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<User> getUsersByRol(Integer roleId) throws RemoteException {
         List<User> users = new ArrayList<User>();
        try {
            StringBuilder sql = new StringBuilder();
             sql.append("SELECT * FROM users WHERE ROLEID = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
             preparedStatement.setInt(1, roleId);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                users.add(getUser(resultSet));
            }
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }
        
       return users;
        
        
        
        //throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Rol getRol(Integer roleId) throws RemoteException, EespaisDatabaseException {
        Rol rol = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select * from all_roles where roleid= ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, roleId);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){
                rol = getRol(resultSet);
            }
        } catch(SQLException ex){
          throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
        
       return rol;
    }
    
    private Rol getRol(ResultSet rs) throws RemoteException, SQLException{
        Rol rol = new Rol();
        rol.setRole_desc(rs.getString("roledescription"));
        rol.setRole_id(rs.getInt("roleid"));
        return rol;
    }

    @Override
    public List<Rol> getRols() throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<User> getUsersByPostalCodes(String postalCode) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    public static void main (String args[]) throws RemoteException {
        UserManager userManager = null;
        try {
            userManager = new UserManagerImpl(DataBaseConnectionManagerImpl.getInstance());
        } catch (RemoteException ex) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("##### Testing UserManagerImpl Methods #####");
        
        System.out.println("1.Retrieving user id=92347");
        User user = null;
        try {
            user = userManager.getUserById("92347");
        } catch (EespaisDatabaseException ex) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(user != null){
            System.out.println("OK: User found her/his name is: " + user.getName());
        } else {
            System.err.println("FAIL: User not found!");
        }
        
        System.out.println("2.Retrieving all users...");
        List<User> users = userManager.getUsers();
        if(users != null && users.size() > 0){
            System.out.println("OK: All Users found! Size = " + users.size());
        } else {
            System.err.println("FAIL: All Users not found!");
        }
        
        System.out.println("2.Retrieving role with id=0");
        Rol rol = null;
        try {
            rol = userManager.getRol(0);
        } catch (EespaisDatabaseException ex) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(rol != null){
            System.out.println("OK: Rol found! Role desc: " + rol.getRole_desc());
        } else {
            System.err.println("FAIL: Rol not found!");
        }
        
    }

}
